package com.toefl.speaking.dao;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.ReadWriteLock;
import java.util.concurrent.locks.ReentrantReadWriteLock;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

import com.toefl.speaking.exception.Logger;
import com.toefl.speaking.util.AppConstans;
import com.toefl.speaking.util.StringUtils;

public class SQLiteHelper {

	private static final String TAG = "SQLiteHelper";

	private final static ReadWriteLock rwl = new ReentrantReadWriteLock();

	public static SQLiteDatabase getReadableDatabase() {
		SQLiteDatabase db = null;
		try {
			rwl.readLock().lock();
			db = SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME, null, SQLiteDatabase.OPEN_READONLY);
		} catch (Exception ex) {
			Logger.printStackTrace(TAG, ex);
		} finally {
			rwl.readLock().unlock();
		}
		return db;
	}

	public static SQLiteDatabase getWritableDatabase() {
		SQLiteDatabase db = null;
		try {
			rwl.writeLock().lock();
			db = SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME, null, SQLiteDatabase.OPEN_READWRITE);
		} catch (Exception ex) {
			Logger.printStackTrace(TAG, ex);
		} finally {
			rwl.writeLock().unlock();
		}
		return db;
	}

	// "/data/data/com.ihandy.xgx/databases/"

	// “/sdcard/”

	private static String DATABASE_PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + "/cts/data/";

	private static String DATABASE_NAME = "toefl.sqlite";

	/**
	 * 初始化数据库
	 * 
	 * @param context
	 */
	public synchronized static void init(final Context ctx) {
		// 输出路径
		String outFileName = DATABASE_PATH + DATABASE_NAME;

		if (!new File(DATABASE_PATH).exists()) {
			File temp = new File(DATABASE_PATH);
			temp.mkdirs();
		}
		// 检测是否已经创建
		// xgx.sqlite-journal 事务失败生成的文件
		File sdcardDir = new File(DATABASE_PATH);
		final File[] mustRemoveFiles = sdcardDir.listFiles(new FilenameFilter() {
			@Override
			public boolean accept(File dir, String filename) {
				return filename.endsWith("-journal");
			}
		});

		if (mustRemoveFiles != null && mustRemoveFiles.length > 0) {
			for (File file : mustRemoveFiles) {
				file.delete();
			}
		}

		File databaseFile = new File(outFileName);
		if (!databaseFile.exists()) {
			InputStream input = null;
			OutputStream output = null;

			try {
				// 从资源中读取数据库流
				input = ctx.getAssets().open(DATABASE_NAME);

				output = new FileOutputStream(outFileName);

				// 拷贝到输出流
				byte[] buffer = new byte[2048];
				int length;
				while ((length = input.read(buffer)) > 0) {
					output.write(buffer, 0, length);
				}
			} catch (FileNotFoundException e) {
				Logger.printStackTrace(TAG, e);
			} catch (IOException e) {
				Logger.printStackTrace(TAG, e);
			} finally {
				// 关闭输出流
				try {
					if (output != null) {
						output.flush();
						output.close();
					}
					if (input != null) {
						input.close();
					}
				} catch (IOException e) {
				}

			}
		}
	}

	/* 删除数据库 */
	public static void deleteDatabaseName() {

		String databaseName = DATABASE_PATH + DATABASE_NAME;
		File sdcardDir = new File(DATABASE_PATH);
		final File[] mustRemoveFiles = sdcardDir.listFiles(new FilenameFilter() {
			@Override
			public boolean accept(File dir, String filename) {
				return filename.endsWith("-journal");
			}
		});

		if (mustRemoveFiles != null && mustRemoveFiles.length > 0) {
			for (File file : mustRemoveFiles) {
				file.delete();
			}
		}

		File databaseFile = new File(databaseName);
		if (databaseFile.exists()) {
			databaseFile.delete();
		}

	}

	/**
	 * 更新数据库
	 * 
	 * @param table
	 * @param values
	 * @param whereClause
	 * @param whereArgs
	 * @return
	 */
	public static boolean update(String table, ContentValues values, String whereClause, String... whereArgs) {
		filterWhereArgs(whereArgs);
		SQLiteDatabase db = getWritableDatabase();
		int affectedRows = 0;
		if (db != null) {
			affectedRows = db.update(table, values, whereClause, whereArgs);
		}
		closeDb(db, null);
		return affectedRows > 0;
	}

	/**
	 * 更新数据库
	 * 
	 * @param table
	 * @param values
	 * @return
	 */
	public static long insert(String table, ContentValues values) {
		long result = 0L;
		SQLiteDatabase db = null;
		;
		try {
			db = getWritableDatabase();
			if (db != null) {
				result = db.insert(table, null, values);
			}
		} catch (Exception e) {
			Log.e("System.out", e.getMessage());
		} finally {
			closeDb(db, null);
		}

		return result;
	}

	/**
	 * 插入或更新
	 * 
	 * @param table
	 * @param values
	 * @param whereClause
	 * @param whereArgs
	 * @return
	 */
	public static boolean insertOrUpdate(String table, ContentValues values, String whereClause, String... whereArgs) {
		filterWhereArgs(whereArgs);
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();
			if (db != null) {
				cursor = db.query(table, null, whereClause, whereArgs, null, null, null);
				if (cursor != null) {
					int count = cursor.getCount();
					if (count > 0) {
						return update(table, values, whereClause, whereArgs);
					} else {
						return insert(table, values) > 0L;
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeDb(db, cursor);
		}
		return false;
	}

	/**
	 * 删除数据库中的表
	 * @param table
	 * @param whereClause
	 * @param whereArgs
	 * @return
	 */
	public static boolean delete(String table, String whereClause, String... whereArgs) {
		boolean result = true;
		SQLiteDatabase db = null;
		try {
			db = getWritableDatabase();
			if (db != null) {
				filterWhereArgs(whereArgs);
				result = db.delete(table, whereClause, whereArgs) > 0;
			}
		} catch (SQLException e) {
			result = false;
			Logger.printStackTrace(TAG, e);
		} catch (Exception e) {
			result = false;
			Logger.printStackTrace(TAG, e);
		} finally {
			if (db != null) {
				db.close();
			}
		}
		return result;
	}

	/**
	 * 查询
	 * 
	 * @param sql
	 * @param whereArgs
	 * @return
	 */
	public static String exeScalar(String sql, String... whereArgs) {
		String uniqueResult = "";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();
			if (db != null) {
				filterWhereArgs(whereArgs);
				cursor = db.rawQuery(sql, whereArgs);
				if (cursor != null && cursor.moveToNext()) {
					uniqueResult = cursor.getString(0);
				}
			}
		} finally {
			closeDb(db, cursor);
		}
		return StringUtils.trimToEmpty(uniqueResult);
	}

	/**
	 * 获取一行记录前N个字段值
	 * 
	 * @param columnCount
	 * @param sql
	 * @param whereArgs
	 * @return
	 */
	public static String[] exeScalar(int columnCount, String sql, String... whereArgs) {
		String[] result = new String[columnCount];
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getWritableDatabase();
			if (db != null) {
				filterWhereArgs(whereArgs);
				cursor = db.rawQuery(sql, whereArgs);
				if (cursor != null && cursor.moveToNext()) {
					int dbCC = cursor.getColumnCount();
					if (columnCount > dbCC) {
						columnCount = dbCC;
					}
					int index = 0;
					while (columnCount > index) {
						result[index] = cursor.getString(index);
						index++;
					}
				}
			}
		} finally {
			closeDb(db, cursor);
		}
		return result;
	}

	/**
	 * 过来whereArgs中为null的数据项
	 * 
	 * @param whereArgs
	 */
	private static void filterWhereArgs(String... whereArgs) {
		if (whereArgs != null && whereArgs.length > 0) {
			for (int i = 0, j = whereArgs.length; i < j; i++) {
				if (whereArgs[i] == null) {
					whereArgs[i] = "";
				}
			}
		}
	}

	/**
	 * 释放数据库资源
	 **/
	public static void closeDb(SQLiteDatabase db, Cursor cursor) {
		if (cursor != null) {
			cursor.close();
		}
		if (db != null) {
			db.close();
		}
	}

	/**
	 * 获得最后一次更新资源的时间
	 */
	public static String getLastUpdateResourceDateTime() {
		String lastUpdateResourceTime = "";
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();
			if (db != null) {
				final String sql = "select * from data_base_version";
				cursor = db.rawQuery(sql, null);
				final int colIndex = cursor.getColumnIndex("resource_last_update_time");
				if (cursor != null && cursor.moveToFirst() && colIndex > -1) {
					lastUpdateResourceTime = cursor.getString(colIndex);
				}
			}
		} catch (Exception e) {
			Logger.printStackTrace(TAG, e);
		} finally {
			closeDb(db, cursor);
		}
		return lastUpdateResourceTime;
	}

	/**
	 * 更新最后一次 资源的更新时间
	 */
	public static void updateLastUpdateResourceDateTime(String serverDateTime) {
		SQLiteDatabase db = null;
		// 开启事务
		try {
			db = getWritableDatabase();
			if (db != null) {
				// 更新客户端数据库版本号
				db.execSQL("update data_base_version set resource_last_update_time = ?",
						new String[] { serverDateTime });
			}
		} catch (Exception e) {
			Logger.printStackTrace(TAG, e);
		} finally {
			if (db != null) {
				db.close();
			}
		}
	}

	/*** 获得所有的资源Map<文件名,文件的大小>,如果没有图片返回空Map */
	public static Map<String, Long> findAllResourceNamesSizePairMap() {
		Map<String, Long> allImgNameMap = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try {
			if (db != null) {
				allImgNameMap = new HashMap<String, Long>(130);
				cursor = db.rawQuery("select resource_name,res_size from resource", null);
				if (cursor != null) {
					while (cursor.moveToNext()) {
						allImgNameMap.put(cursor.getString(0), cursor.getLong(1));
					}
				}
			}
		} finally {
			closeDb(db, cursor);
		}

		if (allImgNameMap == null) {
			allImgNameMap = new HashMap<String, Long>(0);
		}
		return allImgNameMap;
	}

	/***
	 * 更新图片大小
	 * @param resourceName 图片文件名
	 * @param fileSize 图片大小
	 */
	public static void updateResourceSizeByResourceName(final String resourceName, final long fileSize) {
		SQLiteDatabase db = null;
		try {
			db = getWritableDatabase();
			if (db != null) {
				db.execSQL("update resource set res_size =? where resource_name=?", new Object[] { fileSize,
						resourceName });
			}
		} catch (Exception ex) {
			Logger.printStackTrace(TAG, ex);
		} finally {
			closeDb(db, null);
		}
	}

	/***
	 * 获得已经成功下载了的 “需要修改的资源”
	 * @return
	 */
	public static Map<Integer, List<String>> findSuccessDownloadedModifyResource() {
		// Map<修改的资源的版本号,已经成功下载了 此次需要修改的图片>
		Map<Integer, List<String>> downloadedResourceMap = new HashMap<Integer, List<String>>();

		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = getReadableDatabase();
			if (db != null) {
				cursor = db.rawQuery("select version,resource_name from resource_modify_version", null);
				if (cursor != null) {

					while (cursor.moveToNext()) {
						final int modifyResourceVersion = cursor.getInt(0);
						final String resourceName = cursor.getString(1);

						List<String> resourceList = downloadedResourceMap.get(modifyResourceVersion);
						if (resourceList == null) {
							resourceList = new ArrayList<String>();
							downloadedResourceMap.put(modifyResourceVersion, resourceList);
						}
						resourceList.add(resourceName);
					}
				}
			}
		} catch (Exception ex) {
			Logger.printStackTrace(TAG, ex);
		} finally {
			closeDb(db, cursor);
		}
		return downloadedResourceMap;
	}

	/**
	 * 获取当前客户端数据库版本
	 **/
	public static int getCurrentDatabaseVersion() {
		int version = 1;
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			String sql = "select * from data_base_version";
			db = getReadableDatabase();
			if (db != null) {
				cursor = db.rawQuery(sql, null);
				int culIndex = cursor.getColumnIndex("version_num");
				if (cursor != null && cursor.moveToFirst()) {
					version = cursor.getInt(culIndex);
				}
			}
		} catch (Exception e) {
			Logger.printStackTrace(TAG, e);
		} finally {
			SQLiteHelper.closeDb(db, cursor);
		}
		return version;
	}

}
